# 考试信息管理模块功能接口
from flask import Blueprint, request, jsonify, send_from_directory
import exts
import sqlite3
import os
import numpy as np
from flask_jwt_extended import jwt_required, get_jwt
import pandas as pd

exams_blueprint = Blueprint('exams', __name__)


# 考试信息查询
@exams_blueprint.route('/query', methods=['GET'])
@jwt_required()
@exts.permission_required('exams', 'R')
def select_exams():
    """考试信息查询接口"""
    school_id = request.args.get('school_id', get_jwt()['school_id'], type=int)
    exam_id = request.args.get('exam_id', None, type=int)
    exam_name = request.args.get('exam_name', None, type=str)
    examtype = request.args.get('examtype', None, type=int)
    year = request.args.get('year', None, type=str)
    date = request.args.get('date', None, type=str)
    grade = request.args.get('grade', None, type=str)
    student_grade = request.args.get('student_grade', None, type=str)

    page = request.args.get('page', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)
    if page < 1 or page_size < 1:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "page 和 page_size 必须大于 0"
        }), 400

    offset = (page - 1) * page_size

    conditions_current = []
    conditions_history = []
    if school_id:
        conditions_current.append(f"e.school_id = {school_id}")
        conditions_history.append(f"he.school_id = {school_id}")
    if exam_id:
        conditions_current.append(f"e.exam_id = {exam_id}")
        conditions_history.append(f"he.exam_id = {exam_id}")
    if exam_name:
        conditions_current.append(f"e.exam_name LIKE '%{exam_name}%'")
        conditions_history.append(f"he.exam_name LIKE '%{exam_name}%'")
    if examtype:
        conditions_current.append(f"e.examtype = {examtype}")
        conditions_history.append(f"he.examtype = {examtype}")
    if year:
        conditions_current.append(f"e.year LIKE '%{year}%'")
        conditions_history.append(f"he.year LIKE '%{year}%'")
    if date:
        conditions_current.append(f"e.date LIKE '%{date}%'")
        conditions_history.append(f"he.date LIKE '%{date}%'")
    if grade:
        conditions_current.append(f"e.grade LIKE '%{grade}%'")
        conditions_history.append(f"he.grade LIKE '%{grade}%'")
    if student_grade:
        conditions_current.append(f"e.student_grade LIKE '%{student_grade}%'")
        conditions_history.append(f"he.student_grade LIKE '%{student_grade}%'")

    where_clause_current = " AND ".join(conditions_current) if conditions_current else "1=1"
    where_clause_history = " AND ".join(conditions_history) if conditions_history else "1=1"

    sql = f"""
        SELECT * FROM (
            SELECT DISTINCT
                e.exam_id,e.school_id,e.exam_name,e.year,e.date,e.student_grade,
                e.grade,e.examtype,s.school_name
            FROM Exam e
            LEFT JOIN School s ON e.school_id = s.school_id
            WHERE {where_clause_current}
            UNION ALL
            SELECT DISTINCT
                he.exam_id,he.school_id,he.exam_name,he.year,he.date,he.student_grade,
                he.grade,he.examtype,s.school_name
            FROM HistoryExam he
            LEFT JOIN School s ON he.school_id = s.school_id
            WHERE {where_clause_history}
        ) AS combined_results
        ORDER BY exam_id DESC
        LIMIT {page_size} OFFSET {offset}
    """

    sql_count = f"""
        SELECT COUNT(DISTINCT exam_id) AS total FROM (
            SELECT DISTINCT e.exam_id
            FROM Exam e
            LEFT JOIN School s ON e.school_id = s.school_id
            WHERE {where_clause_current}
            UNION ALL
            SELECT DISTINCT he.exam_id
            FROM HistoryExam he
            LEFT JOIN School s ON he.school_id = s.school_id
            WHERE {where_clause_history}
        ) AS subquery
    """

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询总记录数
        cursor.execute(sql_count)
        total_records = cursor.fetchone()[0]
        total_pages = (total_records + page_size - 1) // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 404
        # 查询当前页的记录
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到符合条件的考试信息"
            }), 404

        results = [dict(zip([column[0] for column in cursor.description], row)) for row in results]

        # 获取对应的考试分数线
        for result in results:
            cursor.execute(f"SELECT * FROM SchoolLineD WHERE exam_id = {result['exam_id']} UNION "
                           f"SELECT * FROM SchoolHistoryLineD WHERE exam_id = {result['exam_id']}")
            scorelines = cursor.fetchall()
            if scorelines:
                result['scorelines'] = [dict(zip([column[0] for column in cursor.description], row))
                                        for row in scorelines]
            else:
                result['scorelines'] = []

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(results),
                "results": results
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 考试分数线查询
@exams_blueprint.route('/scorelines', methods=['GET'])
@jwt_required()
@exts.permission_required('exams', 'R')
def select_exams_scoreline():
    """考试分数线查询接口"""
    school_id = request.args.get('school_id', get_jwt()['school_id'], type=int)
    exam_name = request.args.get('exam_name', None, type=str)
    exam_id = request.args.get('exam_id', None, type=int)
    line_name = request.args.get('line_name', None, type=str)
    year = request.args.get('year', None, type=str)
    date = request.args.get('date', None, type=str)
    student_grade = request.args.get('student_grade', None, type=str)
    grade = request.args.get('grade', None, type=str)
    examtype = request.args.get('examtype', None, type=int)
    page = request.args.get('page', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)

    if page < 1 or page_size < 1:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "page 和 page_size 必须大于 0"
        }), 400

    offset = (page - 1) * page_size

    conditions_common = []
    conditions_current = []
    conditions_history = []

    if school_id:
        conditions_common.append(f"(e.school_id = {school_id} OR he.school_id = {school_id})")
    if exam_name:
        conditions_common.append(f"(e.exam_name LIKE '%{exam_name}%' OR he.exam_name LIKE '%{exam_name}%')")
    if exam_id:
        conditions_common.append(f"(e.exam_id = {exam_id} OR he.exam_id = {exam_id})")
    if line_name:
        conditions_current.append(f"sl.line_name LIKE '%{line_name}%'")
        conditions_history.append(f"shl.line_name LIKE '%{line_name}%'")
    if year:
        conditions_common.append(f"(e.year LIKE '%{year}%' OR he.year LIKE '%{year}%')")
    if date:
        conditions_common.append(f"(e.date LIKE '%{date}%' OR he.date LIKE '%{date}%')")
    if student_grade:
        conditions_common.append(
            f"(e.student_grade LIKE '%{student_grade}%' OR he.student_grade LIKE '%{student_grade}%')")
    if grade:
        conditions_common.append(f"(e.grade LIKE '%{grade}%' OR he.grade LIKE '%{grade}%')")
    if examtype is not None:
        conditions_common.append(f"(e.examtype = {examtype} OR he.examtype = {examtype})")

    where_clause_current = " AND ".join(
        conditions_common + conditions_current) if conditions_common or conditions_current else "1=1"
    where_clause_history = " AND ".join(
        conditions_common + conditions_history) if conditions_common or conditions_history else "1=1"

    sql = f"""
        SELECT * FROM (
            SELECT DISTINCT
                sl.id,sl.exam_id,sl.line_name,sl.Sum,sl.SumD,sl.Yuwen,sl.YuwenD,sl.Shuxue,
                sl.ShuxueD,sl.Waiyu,sl.WaiyuD,sl.Xiaoyuzhong,sl.XiaoyuzhongD,sl.Wuli,sl.WuliD,
                sl.Huaxue,sl.HuaxueD,sl.Shengwu,sl.ShengwuD,sl.Lishi,sl.LishiD,sl.Zhengzhi,
                sl.ZhengzhiD,sl.Dili,sl.DiliD,sl.Zonghe,sl.ZongheD,
                COALESCE(e.exam_name, he.exam_name) AS exam_name,
                COALESCE(e.year, he.year) AS year,
                COALESCE(e.date, he.date) AS date,
                COALESCE(e.grade, he.grade) AS grade,
                COALESCE(e.student_grade, he.student_grade) AS student_grade,
                COALESCE(e.examtype, he.examtype) AS examtype,
                s.school_name
            FROM
                SchoolLineD sl
            LEFT JOIN
                Exam e ON sl.exam_id = e.exam_id
            LEFT JOIN
                HistoryExam he ON sl.exam_id = he.exam_id
            LEFT JOIN
                School s ON e.school_id = s.school_id OR he.school_id = s.school_id
            WHERE
                {where_clause_current}
            UNION ALL
            SELECT DISTINCT
                shl.id,shl.exam_id,shl.line_name,shl.Sum,shl.SumD,shl.Yuwen,shl.YuwenD,shl.Shuxue,
                shl.ShuxueD,shl.Waiyu,shl.WaiyuD,shl.Xiaoyuzhong,shl.XiaoyuzhongD,shl.Wuli,shl.WuliD,
                shl.Huaxue,shl.HuaxueD,shl.Shengwu,shl.ShengwuD,shl.Lishi,shl.LishiD,shl.Zhengzhi,
                shl.ZhengzhiD,shl.Dili,shl.DiliD,shl.Zonghe,shl.ZongheD,
                COALESCE(e.exam_name, he.exam_name) AS exam_name,
                COALESCE(e.year, he.year) AS year,
                COALESCE(e.date, he.date) AS date,
                COALESCE(e.grade, he.grade) AS grade,
                COALESCE(e.student_grade, he.student_grade) AS student_grade,
                COALESCE(e.examtype, he.examtype) AS examtype,
                s.school_name
            FROM
                SchoolHistoryLineD shl
            LEFT JOIN
                Exam e ON shl.exam_id = e.exam_id
            LEFT JOIN HistoryExam he 
            ON shl.exam_id = he.exam_id
            LEFT JOIN
                School s ON he.school_id = s.school_id OR e.school_id = s.school_id
            WHERE
                {where_clause_history}
        ) AS combined_results
        ORDER BY id DESC
        LIMIT {page_size} OFFSET {offset}
    """

    sql_count = f"""
        SELECT COUNT(DISTINCT id) AS total FROM (
            SELECT DISTINCT sl.id
            FROM SchoolLineD sl
            LEFT JOIN Exam e ON sl.exam_id = e.exam_id
            LEFT JOIN HistoryExam he ON sl.exam_id = he.exam_id
            LEFT JOIN School s ON e.school_id = s.school_id OR he.school_id = s.school_id
            WHERE {where_clause_current}
            UNION ALL
            SELECT DISTINCT shl.id
            FROM SchoolHistoryLineD shl
            LEFT JOIN HistoryExam he ON shl.exam_id = he.exam_id
            LEFT JOIN Exam e ON shl.exam_id = e.exam_id
            LEFT JOIN School s ON he.school_id = s.school_id OR e.school_id = s.school_id
            WHERE {where_clause_history}
        ) AS subquery
    """
    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询总记录数
        cursor.execute(sql_count)
        total_records = cursor.fetchone()[0]
        total_pages = (total_records + page_size - 1) // page_size
        if total_records == 0:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到符合条件的分数线记录"
            }), 404
        if 0 < total_pages < page:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 404
        # 查询当前页的记录
        cursor.execute(sql)
        results = cursor.fetchall()

        results = [dict(zip([column[0] for column in cursor.description], row)) for row in results]

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(results),
                "results": results
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()


# 考试信息删除
@exams_blueprint.route('/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('exams', 'D')
def delete_exams():
    """考试信息删除接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        results = []
        for item in data:
            cur.execute("DELETE FROM Exam WHERE exam_id = ?", (item,))
            if cur.rowcount == 0:
                cur.execute("DELETE FROM HistoryExam WHERE exam_id = ?", (item,))
                if cur.rowcount == 0:
                    results.append({"exam_id": item, "status": "error", "message": "未找到指定记录"})
                else:
                    results.append({"exam_id": item, "status": "success", "message": "删除成功"})
            else:
                results.append({"exam_id": item, "status": "success", "message": "删除成功"})
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "请求成功",
            "data": results
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 考试分数线删除
@exams_blueprint.route('/scorelines/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('exams', 'D')
def delete_scorelines():
    """考试分数线删除接口"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        results = []
        for item in data:
            cur.execute("DELETE FROM SchoolLineD WHERE id = ?", (item,))
            if cur.rowcount == 0:
                cur.execute("DELETE FROM SchoolHistoryLineD WHERE id = ?", (item,))
                if cur.rowcount == 0:
                    results.append({"id": item, "status": "error", "message": "未找到指定记录"})
                else:
                    results.append({"id": item, "status": "success", "message": "删除成功"})
            else:
                results.append({"id": item, "status": "success", "message": "删除成功"})
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "请求成功",
            "data": results
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 考试信息数据迁移
@exams_blueprint.route('/migrate', methods=['POST'])
@jwt_required()
@exts.permission_required('exams', 'M')
def exams_m():
    """考试信息数据迁移接口"""
    sql = '''
    INSERT INTO HistoryExam
    SELECT * FROM Exam;
    DELETE FROM Exam;
    '''
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        cur.executescript(sql)
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "数据迁移成功"
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 考试分数线迁移
@exams_blueprint.route('/scorelines/migrate', methods=['POST'])
@jwt_required()
@exts.permission_required('exams', 'M')
def scorelines_m():
    """考试分数线数据迁移接口"""
    sql = '''
    INSERT INTO SchoolHistoryLineD
    SELECT * FROM SchoolLineD;
    DELETE FROM SchoolLineD;
    '''
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        cur.executescript(sql)
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "数据迁移成功"
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 新增考试信息
@exams_blueprint.route('/add', methods=['POST'])
@jwt_required()
@exts.permission_required('exams', 'C')
def add_exam():
    """新增考试信息接口"""
    data = {
        "exam_name": request.json.get('exam_name', None),
        "examtype": request.json.get('examtype', None),
        "year": request.json.get('year', None),
        "date": request.json.get('date', None),
        "grade": request.json.get('grade', None),
        "student_grade": request.json.get('student_grade', None),
        "school_id": request.json.get('school_id', None),
    }
    if (data['exam_name'] is None or data['year'] is None
            or data['grade'] is None or data['student_grade'] is None):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "缺少必要的参数, 请确保 exam_name, year, grade, student_grade 参数不为空"
        }), 400
    if data['school_id'] is None:
        claims = get_jwt()
        data['school_id'] = claims['school_id']
    new_id = exts.get_latest_id("exams")
    if new_id is None:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": "无法获取最新的考试ID。"
        }), 500
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        sql = f'INSERT INTO Exam (exam_id, exam_name, examtype, year, date, grade, student_grade, school_id) ' \
              f'VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
        cur.execute(sql, (new_id, data['exam_name'], data['examtype'], data['year'], data['date'], data['grade'],
                          data['student_grade'], data['school_id']))
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "考试信息添加成功",
            "data": {"exam_id": cur.lastrowid}
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"请求无法完成，服务器遇到了意外的情况。错误详情：{e}"
        }), 500


# 新增考试分数线
@exams_blueprint.route('/scorelines/add', methods=['POST'])
@jwt_required()
@exts.permission_required('exams', 'C')
def add_scoreline():
    """新增考试分数线接口"""
    params = {
        "exam_id": request.json.get('exam_id', None),
        "line_name": request.json.get('line_name', None),
        "Sum": request.json.get('Sum', None),
        "Yuwen": request.json.get('Yuwen', None),
        "Shuxue": request.json.get('Shuxue', None),
        "Waiyu": request.json.get('Waiyu', None),
        "Xiaoyuzhong": request.json.get('Xiaoyuzhong', None),
        "Wuli": request.json.get('Wuli', None),
        "Huaxue": request.json.get('Huaxue', None),
        "Shengwu": request.json.get('Shengwu', None),
        "Lishi": request.json.get('Lishi', None),
        "Zhengzhi": request.json.get('Zhengzhi', None),
        "Dili": request.json.get('Dili', None),
        "Zonghe": request.json.get('Zonghe', None)
    }
    data = ['Sum', 'Yuwen', 'Shuxue', 'Waiyu', 'Xiaoyuzhong', 'Wuli', 'Huaxue', 'Shengwu', 'Lishi', 'Zhengzhi', 'Dili',
            'Zonghe']
    if (params['exam_id'] is None or params['line_name'] is None or params['Sum'] is None
            or params['Yuwen'] is None or params['Shuxue'] is None):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "缺少必要的参数, 请确保 exam_id, line_name, Sum, Yuwen, Shuxue 参数不为空"
        }), 400
    # 删除空值参数
    params = {key: value for key, value in params.items() if value is not None}
    data = [key for key in data if key in params]
    new_id = exts.get_latest_id("scoreLines")
    if new_id is None:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": "无法获取最新的分数线ID。"
        }), 500
    params['id'] = new_id
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 检查exam_id是否存在
        cur.execute("SELECT exam_id FROM Exam WHERE exam_id = ? "
                    "UNION SELECT exam_id FROM HistoryExam WHERE exam_id = ?",
                    (params['exam_id'], params['exam_id']))
        if not cur.fetchone():
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求参数错误",
                "details": f"exam_id 为 {params['exam_id']} 的考试场次不存在"
            }), 404
        if "Wuli" in data:
            conditions = " AND Wuli IS NOT NULL AND Wuli != 0"
        elif "Lishi" in data:
            conditions = " AND Lishi IS NOT NULL AND Lishi != 0"
        else:
            conditions = ""
        # 查找分数对应的段次
        for key in data:
            sql = f"""SELECT {key + 'D'}
            FROM CurrentScoreD
            WHERE {key} >= {params[key]} AND exam_id = {params['exam_id']} {conditions}
            UNION
            SELECT {key + 'D'}
            FROM HistoryScoreD
            WHERE {key} >= {params[key]} AND exam_id = {params['exam_id']} {conditions}
            ORDER BY {key + 'D'} DESC
            LIMIT 1"""
            if key == 'Waiyu':
                sql = sql.replace("!= 0", "!= 0 AND (WaiyuType = 1 OR WaiyuType IS NULL)")
            elif key == 'Xiaoyuzhong':
                sql = sql.replace("Xiaoyuzhong", "Waiyu")
                sql = sql.replace("!= 0", "!= 0 AND WaiyuType = 2")
            cur.execute(sql)
            result = cur.fetchone()
            if result:
                params[key + 'D'] = result[0]
            else:
                params[key + 'D'] = 0
        sql = f"INSERT INTO SchoolLineD ({', '.join(params.keys())}) VALUES ({', '.join(['?'] * len(params))})"
        cur.execute(sql, tuple(params.values()))
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "分数线添加成功",
            "data": {"id": cur.lastrowid}
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 更新考试信息
@exams_blueprint.route('/update/<int:exam_id>', methods=['PUT'])
@jwt_required()
@exts.permission_required('exams', 'U')
def update_exam(exam_id):
    """更新考试信息接口"""
    # 可更新的参数列表
    valid_params = ['exam_name', 'examtype', 'year', 'date', 'grade', 'student_grade', 'school_id']
    # 必需的参数列表
    required_params = ['exam_name', 'year', 'grade', 'student_grade']

    data = {key: request.json.get(key) for key in valid_params}

    if data['school_id'] is None:
        claims = get_jwt()
        data['school_id'] = claims['school_id']

    # 检查可更新的字段，如果不存在，则删除
    for key in valid_params:
        if key not in request.json:
            data.pop(key)

    # 如果参数为空，则返回 400 错误
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的更新字段"
        }), 400

    # 检查必需字段，如果存在，则不允许为空
    for key in required_params:
        if key in request.json and request.json[key] is None:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"缺少必要的参数, 请确保 {key} 参数不为空"
            }), 400

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()

        set_clause = ", ".join([f"{key} = ?" for key in data.keys()])
        sql = f'UPDATE Exam SET {set_clause} WHERE exam_id = ?'
        params = tuple(data.values()) + (exam_id,)

        cur.execute(sql, params)

        if cur.rowcount == 0:
            sql = f'UPDATE HistoryExam SET {set_clause} WHERE exam_id = ?'
            cur.execute(sql, params)
            # 如果仍然没有更新任何记录，返回 404 错误
            if cur.rowcount == 0:
                return jsonify({
                    "code": 404,
                    "status": "error",
                    "message": "指定的记录不存在",
                    "details": f"没有找到 id 为 {exam_id} 的记录。"
                }), 404

        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "考试信息更新成功",
            "data": {"exam_id": exam_id}
        }), 200

    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500

    finally:
        if conn:
            exts.close_db(conn)


# 更新考试分数线
@exams_blueprint.route('/scorelines/update/<int:id_>', methods=['PUT'])
@jwt_required()
@exts.permission_required('exams', 'U')
def update_scoreline(id_):
    """更新考试分数线接口"""
    params = {
        "exam_id": request.json.get('exam_id', None),
        "line_name": request.json.get('line_name', None),
        "Sum": request.json.get('Sum', None),
        "Yuwen": request.json.get('Yuwen', None),
        "Shuxue": request.json.get('Shuxue', None),
        "Waiyu": request.json.get('Waiyu', None),
        "Xiaoyuzhong": request.json.get('Xiaoyuzhong', None),
        "Wuli": request.json.get('Wuli', None),
        "Huaxue": request.json.get('Huaxue', None),
        "Shengwu": request.json.get('Shengwu', None),
        "Lishi": request.json.get('Lishi', None),
        "Zhengzhi": request.json.get('Zhengzhi', None),
        "Dili": request.json.get('Dili', None),
        "Zonghe": request.json.get('Zonghe', None)
    }
    data = ['Sum', 'Yuwen', 'Shuxue', 'Waiyu', 'Xiaoyuzhong', 'Wuli', 'Huaxue', 'Shengwu', 'Lishi', 'Zhengzhi', 'Dili',
            'Zonghe']

    if (params['exam_id'] is None or params['line_name'] is None or params['Sum'] is None
            or params['Yuwen'] is None or params['Shuxue'] is None):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "缺少必要的参数, 请确保 exam_id, line_name, Sum, Yuwen, Shuxue 参数不为空"
        }), 400

    # 删除空值参数
    params = {key: value for key, value in params.items() if value is not None}
    data = [key for key in data if key in params]

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()

        # 检查exam_id是否存在
        cur.execute("SELECT exam_id FROM Exam WHERE exam_id = ? "
                    "UNION SELECT exam_id FROM HistoryExam WHERE exam_id = ?",
                    (params['exam_id'], params['exam_id']))
        if not cur.fetchone():
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求参数错误",
                "details": f"exam_id 为 {params['exam_id']} 的考试场次不存在"
            }), 404

        if "Wuli" in data:
            conditions = " AND Wuli IS NOT NULL AND Wuli != 0"
        elif "Lishi" in data:
            conditions = " AND Lishi IS NOT NULL AND Lishi != 0"
        else:
            conditions = ""

        # 查找分数对应的段次
        for key in data:
            sql = f"""SELECT {key + 'D'}
            FROM CurrentScoreD
            WHERE {key} >= {params[key]} AND exam_id = {params['exam_id']} {conditions}
            UNION
            SELECT {key + 'D'}
            FROM HistoryScoreD
            WHERE {key} >= {params[key]} AND exam_id = {params['exam_id']} {conditions}
            ORDER BY {key + 'D'} DESC
            LIMIT 1"""
            if key == 'Waiyu':
                sql = sql.replace("!= 0", "!= 0 AND (WaiyuType = 1 OR WaiyuType IS NULL)")
            elif key == 'Xiaoyuzhong':
                sql = sql.replace("Xiaoyuzhong", "Waiyu")
                sql = sql.replace("!= 0", "!= 0 AND WaiyuType = 2")
            cur.execute(sql)
            result = cur.fetchone()
            if result:
                params[key + 'D'] = result[0]
            else:
                params[key + 'D'] = 0

        # 更新记录
        set_clause = ", ".join([f"{key} = ?" for key in params.keys()])
        sql = f'UPDATE SchoolLineD SET {set_clause} WHERE id = ?'
        update_params = tuple(params.values()) + (id_,)

        cur.execute(sql, update_params)

        if cur.rowcount == 0:
            sql = f'UPDATE SchoolHistoryLineD SET {set_clause} WHERE id = ?'
            cur.execute(sql, update_params)

            if cur.rowcount == 0:
                return jsonify({
                    "code": 404,
                    "status": "error",
                    "message": "指定的记录不存在",
                    "details": f"没有找到 id 为 {id_} 的记录。"
                }), 404

        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "考试分数线更新成功",
            "data": {"id": id_}
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            conn.close()


# 上传文件新增考试分数线
@exams_blueprint.route('/scorelines/import', methods=['POST'])
@jwt_required()
@exts.permission_required('exams', 'C')
def import_scoreline():
    """上传文件新增考试分数线"""
    exam_id = request.form.get('exam_id')
    if not exam_id:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "缺少必要的考试ID"
        }), 400
    file = request.files.get('file')
    if not file or file.filename == '':
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未上传文件"
        }), 400
    if not file.filename.endswith('.xlsx'):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "上传的文件格式不正确"
        }), 400
    results = None
    try:
        df = pd.read_excel(file, sheet_name='Sheet1')
        results = exts.update_scoreline(df, {"exam_id": exam_id})
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    if results:
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "文件处理完成",
            "details": results
        }), 200


# 分数线上传模板下载
@exams_blueprint.route('/download/scoreline-template/<string:template_code>', methods=['GET'])
@jwt_required()
@exts.permission_required('exams', 'R')
def download_scoreline_template(template_code):
    """根据模板代码下载分数线上传模板"""
    template_filenames = {
        "1": "分数线上传模板（通用）.xlsx"
    }
    template_filename = template_filenames.get(template_code)
    if not template_filename:
        # 如果提供的代码不在映射表中，返回错误信息
        return jsonify({
            "code": 400,
            "details": "提供的模板代码无效",
            "message": "请求参数错误",
            "status": "error"
        }), 400

    template_path = os.path.join("static/upload-templates/scoreline-templates", template_filename)
    if not os.path.exists(template_path):
        # 如果文件不存在，返回错误信息
        return jsonify({
            "code": 404,
            "details": f"模板文件'{template_filename}'不存在",
            "message": "请求参数错误",
            "status": "error"
        }), 404

    try:
        return send_from_directory("static/upload-templates/scoreline-templates", template_filename, as_attachment=True)
    except Exception as e:
        return jsonify({
            "code": 500,
            "details": str(e),
            "message": "下载模板时发生错误",
            "status": "error"
        }), 500
